package OOPAndMultiThreadArchiveManager.Server.Entities;

import OOPAndMultiThreadArchiveManager.Server.Tools.PropertiesLoader;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class DbContext {
    private Connection connection;
    static String JDBC_DRIVER = "";
    static String DB_URL = "";
    static String USER = "";
    static String PASS = "";
    public DbContext() {
        try {
            JDBC_DRIVER = PropertiesLoader.JDBC_DRIVER;
            DB_URL = PropertiesLoader.DataBaseURL;
            USER = PropertiesLoader.DataBaseUser;
            PASS = PropertiesLoader.DataBasePassword;
            Class.forName(JDBC_DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public <T> int insert(T item) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            var map = new LinkedHashMap<String, Object>();
            objectToMap(item, map);
            var sqlBuilder = new StringBuilder();
            var keys = new ArrayList<String>();
            map.forEach((key, value) -> keys.add(key));
            sqlBuilder.append("INSERT INTO ")
                    .append(getLastClassName(item.getClass()))
                    .append("(")
                    .append(String.join(",", keys))
                    .append(")VALUES(")
                    .append(String.join(",", Collections.nCopies(keys.size(), "?")))
                    .append(");");
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            for (int i = 0; i < map.size(); i++) {
                var itemType = map.get(keys.get(i)).getClass();
                if (itemType.equals(String.class)) preparedStatement.setString(i + 1, (String) map.get(keys.get(i)));
                else if (itemType.equals(java.util.Date.class)) preparedStatement.setTimestamp(i + 1, new Timestamp (((java.util.Date) map.get(keys.get(i))).getTime()));
                else if (itemType.equals(Integer.class)) preparedStatement.setInt(i + 1, (int) map.get(keys.get(i)));
            }
            var result = preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
            return result;
        } catch (Exception ignored) {

        }
        return 0;
    }

    public <T> int update(T item, List<String> queryKeys, List<?> queryValues) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            var map = new LinkedHashMap<String, Object>();
            objectToMap(item, map);
            var sqlBuilder = new StringBuilder();
            var keys = new ArrayList<String>();
            map.forEach((key, value) -> keys.add(key));
            sqlBuilder.append("UPDATE ")
                    .append(getLastClassName(item.getClass()))
                    .append(" SET ")
                    .append(String.join("=?,", keys))
                    .append("=? WHERE ")
                    .append(String.join("=? AND ", queryKeys))
                    .append("=?;");
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            var i = 0;
            for (; i < map.size(); i++) {
                var itemType = map.get(keys.get(i)).getClass();
                if (itemType.equals(String.class)) preparedStatement.setString(i + 1, (String) map.get(keys.get(i)));
                else if (itemType.equals(java.util.Date.class)) preparedStatement.setTimestamp(i + 1, new Timestamp (((java.util.Date) map.get(keys.get(i))).getTime()));
                else if (itemType.equals(Integer.class)) preparedStatement.setInt(i + 1, (int) map.get(keys.get(i)));
            }
            for (; i < map.size() + queryValues.size(); i++) {
                var itemType = queryValues.get(i - map.size()).getClass();
                if (itemType.equals(String.class)) preparedStatement.setString(i + 1, (String) queryValues.get(i - map.size()));
                else if (itemType.equals(java.util.Date.class)) preparedStatement.setTimestamp(i + 1, new Timestamp (((java.util.Date) queryValues.get(i - map.size())).getTime()));
                else if (itemType.equals(Integer.class)) preparedStatement.setInt(i + 1, (int) queryValues.get(i - map.size()));
            }
            var result = preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
            return result;
        } catch (Exception ignored) {

        }
        return 0;
    }

    public <T> List<T> select(T item, List<String> queryKeys){
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        var result = new ArrayList<T>();
        var type = item.getClass();
        try{
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            var map = new LinkedHashMap<String, Object>();
            objectToMap(item, map);
            var queryValues = new ArrayList<>();
            map.forEach((key, value) -> {
                if (queryKeys.contains(key)) queryValues.add(value);
            });
            var sqlBuilder = new StringBuilder();
            sqlBuilder.append("SELECT * FROM ")
                    .append(getLastClassName(type));
            if (queryKeys.size() == 0){
                sqlBuilder.append(";");
            } else {
                sqlBuilder.append(" WHERE ")
                        .append(String.join("=? AND ", queryKeys))
                        .append("=?;");
            }
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            for (int i = 0; i < queryValues.size(); i++) {
                var itemType = queryValues.get(i).getClass();
                if (itemType.equals(String.class)) preparedStatement.setString(i + 1, (String) queryValues.get(i));
                else if (itemType.equals(java.util.Date.class)) preparedStatement.setTimestamp(i + 1, new Timestamp (((java.util.Date) queryValues.get(i)).getTime()));
                else if (itemType.equals(Integer.class)) preparedStatement.setInt(i + 1, (int) queryValues.get(i));
            }
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                var instance = (T) reflectObject(resultSet, type);
                result.add(instance);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
            return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public <T> int delete(T item, List<String> queryKeys){
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        var type = item.getClass();
        try{
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            var map = new LinkedHashMap<String, Object>();
            objectToMap(item, map);
            var queryValues = new ArrayList<>();
            map.forEach((key, value) -> {
                if (queryKeys.contains(key)) queryValues.add(value);
            });
            var sqlBuilder = new StringBuilder();
            sqlBuilder.append("DELETE FROM ")
                    .append(getLastClassName(type))
                    .append(" WHERE ")
                    .append(String.join("=? AND ", queryKeys))
                    .append("=?;");
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            for (int i = 0; i < queryValues.size(); i++) {
                var itemType = queryValues.get(i).getClass();
                if (itemType.equals(String.class)) preparedStatement.setString(i + 1, (String) queryValues.get(i));
                else if (itemType.equals(java.util.Date.class)) preparedStatement.setTimestamp(i + 1, new Timestamp (((java.util.Date) queryValues.get(i)).getTime()));
                else if (itemType.equals(Integer.class)) preparedStatement.setInt(i + 1, (int) queryValues.get(i));
            }
            var result = preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
            return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private <T> T reflectObject(ResultSet resultSet, Class<T> type) throws Exception {
        var instance = type.getDeclaredConstructor().newInstance();
        var declaredFields = type.getDeclaredFields();
        for(Field declaredField: declaredFields){
            declaredField.setAccessible(true);
            var fieldName = declaredField.getName();
            var fieldType = declaredField.getType();
            declaredField.set(instance, getFieldValue(resultSet, fieldName, fieldType));
        }
        return instance;
    }
    private <T> T getFieldValue(ResultSet resultSet, String fieldName, Class<T> type) throws SQLException {
        T object = null;
        if (isExistColumn(resultSet, fieldName)) {
            object = (T) resultSet.getObject(fieldName, type);
        }
        return object;
    }
    private boolean isExistColumn(ResultSet resultSet, String fieldName) {
        try {
            resultSet.findColumn(fieldName);
            return true;
        } catch (SQLException throwable) {
            return false;
        }
    }
    private<T> String getLastClassName(Class<T> type){
        var className = Arrays.stream(type.getName().split("\\.")).toList();
        return className.get(className.size() - 1);
    }
    private<T> void objectToMap(T item, LinkedHashMap<String, Object> map) throws IllegalAccessException {
        var type = item.getClass();
        var fields = type.getDeclaredFields();
        for (var field : fields) {
            field.setAccessible(true);
            var KeyName = field.getName();
            if (KeyName.equals("id")) continue;
            var value = field.get(item);
            map.put(KeyName, value);
        }
    }
}
